package devicedbgui;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/** Interface to database for acquiring Device information */
public class DeviceDAO {
    /** Stores Connection status */
    private Connection conn;
    /** Constructor used to pass connection status to object
     * @param conn  connection status
     */
    public DeviceDAO(Connection conn)
    { 
        this.conn = conn;
    }
    /** Selects all devices in database
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Device> selectAll() throws Exception {
        ArrayList<Device> result = new ArrayList<Device>();

        try {
            String query = "SELECT * FROM Device;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Device pObj = new Device(
                        rs.getString("record"),
                        rs.getString("mac"),
                        rs.getString("serial"),
                        rs.getString("name"),
                        rs.getString("type"));
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
    /** Searches database by particular record
     * 
     * @param record 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Device> searchByRecord(String record) throws Exception {
        ArrayList<Device> result = new ArrayList<Device>();

        try {
            String query = "SELECT * FROM Device where record like '%" + record +"%';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Device pObj = new Device(
                        rs.getString("record"),
                        rs.getString("mac"),
                        rs.getString("serial"),
                        rs.getString("name"),
                        rs.getString("type"));
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }

        return result;
    }
    /** Searches database by particular MAC address
     * 
     * @param mac 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Device> searchByMAC(String mac) throws Exception {
        ArrayList<Device> result = new ArrayList<Device>();

        try {
            String query = "SELECT * FROM Device where mac like '%" + mac +"%';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Device pObj = new Device(
                        rs.getString("record"),
                        rs.getString("mac"),
                        rs.getString("serial"),
                        rs.getString("name"),
                        rs.getString("type"));
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }

        return result;
    }
    /** Searches database by particular serial
     * 
     * @param serial 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Device> searchBySerial(String serial) throws Exception {
        ArrayList<Device> result = new ArrayList<Device>();

        try {
            String query = "SELECT * FROM Device where serial like '%" + serial +"%';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Device pObj = new Device(
                        rs.getString("record"),
                        rs.getString("mac"),
                        rs.getString("serial"),
                        rs.getString("name"),
                        rs.getString("type"));
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }

        return result;
    }
    /** Searches database by device name
     * 
     * @param name 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Device> searchByName(String name) throws Exception {
        ArrayList<Device> result = new ArrayList<Device>();

        try {
            String query = "SELECT * FROM Device where name like '%" + name +"%';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Device pObj = new Device(
                        rs.getString("record"),
                        rs.getString("mac"),
                        rs.getString("serial"),
                        rs.getString("name"),
                        rs.getString("type"));
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }

        return result;
    }
    /** Searches database by device type
     * 
     * @param type 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Device> searchByType(String type) throws Exception {
        ArrayList<Device> result = new ArrayList<Device>();

        try {
            String query = "SELECT * FROM Device where type like '%" + type +"%';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Device pObj = new Device(
                        rs.getString("record"),
                        rs.getString("mac"),
                        rs.getString("serial"),
                        rs.getString("name"),
                        rs.getString("type"));
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }

        return result;
    }
    /** Searches database by particular id
     * 
     * @param pId 
     * @return result
     * @throws java.lang.Exception
     */
    public Device selectById(String pId) throws Exception {
        String query = "SELECT * FROM Device WHERE " +
                "record = '" + pId + "';";
        
        try {
            
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Device pObj = new Device(
                        rs.getString("record"),
                        rs.getString("mac"),
                        rs.getString("serial"),
                        rs.getString("name"),
                        rs.getString("type"));
                
                return pObj;
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return null;
    }
    /** Inserts an new device record into database
     * 
     * @param pObj
     * @throws java.lang.Exception
     */
    public void insert(Device pObj) throws Exception {
       
        try {
            String insert = "INSERT INTO Device(" +
                    "record," +
                    "mac," +
                    "serial," +
                    "name," +
                    "type)" +
                    "VALUES('" + pObj.record + "','" +
                                 pObj.MAC + "','" +
                                 pObj.serial + "','" +
                                 pObj.name + "','" +
                                 pObj.type + "');";

            
            PreparedStatement pstmt = conn.prepareStatement(insert);
            pstmt.executeUpdate();            

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Updates database with the modified build information 
 * 
 * @param pObj
 * @throws java.lang.Exception
 */
    public void update(Device pObj) throws Exception {        
        try {

            String update = "UPDATE Device SET " +
                    "mac = '" + pObj.MAC + "'," +
                    "serial = '" + pObj.serial +  "'," +
                    "name = '" + pObj.name +  "'," +
                    "type = '" + pObj.type +
                    "' WHERE record = '" + pObj.record + "';";
            
           
            PreparedStatement pstmt = conn.prepareStatement(update);
            pstmt.executeUpdate();
            pstmt.close();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
/** Deletes a value from database
 * 
 * @param pId
 * @throws java.lang.Exception
 */
    public void delete(String pId) throws Exception {
       
        try {
            String delete = "DELETE FROM Device WHERE record = '" + pId + "';";
          
            PreparedStatement pstmt = conn.prepareStatement(delete);
            pstmt.executeUpdate();
           

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }
       /** Chooses builds based on device record
     * 
     * @param Device_record
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Build> selectBuildsByDevice(String Device_record) throws Exception {
        ArrayList<Build> result = new ArrayList<Build>();
        try {
            String query = "SELECT * FROM Build where Device_record = '" + Device_record +"';";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Build pObj = new Build(
                        rs.getInt("idBuild"),
                        rs.getDate("start"),
                        rs.getDate("end"),
                        rs.getString("Device_record")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
       /** Chooses exe values based on build id
     * 
        * @param idBuild 
        * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Exe> selectExesByBuild(int idBuild) throws Exception {
        ArrayList<Exe> result = new ArrayList<Exe>();
        try {
            String query = "SELECT * FROM exe where Build_idBuild = " + idBuild +";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Exe pObj = new Exe(
                        rs.getInt("idexe"),
                        rs.getString("description"),                        
                        rs.getInt("Build_idBuild")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
       /** Chooses repair info based on build id
     * 
        * @param idBuild 
        * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<RepairInfo> selectRepairInfosByBuild(int idBuild) throws Exception {
        ArrayList<RepairInfo> result = new ArrayList<RepairInfo>();
        try {
            String query = "SELECT * FROM RepairInfo where Build_idBuild = " + idBuild +";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RepairInfo pObj = new RepairInfo(
                        rs.getInt("idRepairInfo"),
                        rs.getDate("timeEntered"),                        
                        rs.getString("description"),                        
                        rs.getString("timeSpent"),                        
                        rs.getString("changesMade"),                        
                        rs.getString("costEstimate"),                        
                        rs.getInt("Build_idBuild")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
   /** Chooses test case results based on associated exe file
     * 
    * @param idexe 
    * @return result
     * @throws java.lang.Exception
     */    
    public ArrayList<TestCase> selectTestCaseByExe(int idexe) throws Exception {
        ArrayList<TestCase> result = new ArrayList<TestCase>();
        try {
            String query = "SELECT * FROM TestCase where exe_idexe = " + idexe +";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestCase pObj = new TestCase( 
                        rs.getInt("idTestCase"),                                               
                        rs.getString("description"),
                        rs.getInt("exe_idexe")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
       /** Chooses test result info based on the test case id
     * 
        * @param idTestCase 
        * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> selectTestResultsByTestCase(int idTestCase) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();
        try {
            String query = "SELECT * FROM TestResult where TestCase_idTestCase = " + idTestCase +";";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getDate("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
       /** Chooses test results based on device record
     * 
        * @param deviceRecord 
        * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> selectTestResultsByDevice(String deviceRecord) throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();
        try {
            String query = "SELECT tr.* FROM Build b, exe e, TestCase tc, TestResult tr " + 
                    " where b.Device_record = '" + deviceRecord +
                    "' and b.idBuild = e.Build_idBuild and e.idexe = tc.exe_idexe and tc.idTestCase = tr.TestCase_idTestCase;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getDate("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
       /** Chooses all builds in database
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Build> selectAllBuilds() throws Exception {
        ArrayList<Build> result = new ArrayList<Build>();

        try {
            String query = "SELECT * FROM Build;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Build pObj = new Build(
                        rs.getInt("idBuild"),
                        rs.getDate("start"),
                        rs.getDate("end"),
                        rs.getString("Device_record")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
       /** Chooses all exe files in database
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Exe> selectAllExes() throws Exception {
        ArrayList<Exe> result = new ArrayList<Exe>();

        try {
            String query = "SELECT * FROM exe;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Exe pObj = new Exe(
                        rs.getInt("idexe"),
                        rs.getString("description"),                        
                        rs.getInt("Build_idBuild")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
       /** Chooses all MAC addresses in database
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<MAC> selectAllMACs() throws Exception {
        ArrayList<MAC> result = new ArrayList<MAC>();

        try {
            String query = "SELECT * FROM MAC;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                MAC pObj = new MAC(
                        rs.getString("purchasedMAC"),
                        rs.getDate("timeAssigned"),
                        rs.getString("Device_record")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
       /** Chooses all repair information in database
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<RepairInfo> selectAllRepairInfos() throws Exception {
        ArrayList<RepairInfo> result = new ArrayList<RepairInfo>();

        try {
            String query = "SELECT * FROM RepairInfo;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                RepairInfo pObj = new RepairInfo(
                        rs.getInt("idRepairInfo"),
                        rs.getDate("timeEntered"),                        
                        rs.getString("description"),                        
                        rs.getString("timeSpent"),                        
                        rs.getString("changesMade"),                        
                        rs.getString("costEstimate"),                        
                        rs.getInt("Build_idBuild")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
     /** Chooses all serial values in database
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<Serial> selectAllSerials() throws Exception {
        ArrayList<Serial> result = new ArrayList<Serial>();

        try {
            String query = "SELECT * FROM Serial;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Serial pObj = new Serial(
                        rs.getString("purchasedSerial"),
                        rs.getDate("timeAssigned"),
                        rs.getString("Device_record")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
     /** Chooses all testcases in database
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestCase> selectAllTestCases() throws Exception {
        ArrayList<TestCase> result = new ArrayList<TestCase>();

        try {
            String query = "SELECT * FROM TestCase;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestCase pObj = new TestCase(
                        rs.getInt("idTestCase"),                                               
                        rs.getString("description"),
                        rs.getInt("exe_idexe")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
     /** Chooses all testcases in database
     * 
     * @return result
     * @throws java.lang.Exception
     */
    public ArrayList<TestResult> selectAllTestResults() throws Exception {
        ArrayList<TestResult> result = new ArrayList<TestResult>();

        try {
            String query = "SELECT * FROM TestResult;";           
            PreparedStatement pstmt = conn.prepareStatement(query);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                TestResult pObj = new TestResult(
                        rs.getInt("idTestResult"),   
                        rs.getString("name"),
                        rs.getString("description"),
                        rs.getDate("time"),
                        rs.getInt("TestCase_idTestCase")
                        );
                result.add(pObj);
            }

        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
        return result;
    }
}
